package com.zzb.projectfactory.framework.utils;

import com.alibaba.druid.pool.vendor.MySqlValidConnectionChecker;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;
import java.util.Set;

/**
 * 数据库工具
 * Created by zhangzhenbin on 17-2-23.
 */
public class DatabaseUtils {


    /**检查数据库的连通性*/
    public static boolean verifyDatabaseLink(final String host,
                                             final Integer port,
                                             final String userName,
                                             final String password,
                                             final String databaseName){
        MySqlValidConnectionChecker mySqlValidConnectionChecker = new MySqlValidConnectionChecker();
        String url = getDatabaseUrlByDatabaseName(host,port,databaseName);

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, userName, password);
            boolean validConnection = mySqlValidConnectionChecker.isValidConnection(connection, "SELECT 1", 3000);
            connection.close();
            return validConnection;
        } catch (Exception e) {
            return false;
        }
    }

    /**通过sql找到操作主表的tableName*/
    public static String getTableName(final String sql){
        List<SQLStatement> statementList = com.alibaba.druid.sql.SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);

        if (!statementList.isEmpty()) {
            SQLStatement sqlStmt = statementList.get(0);
            SchemaStatVisitor visitor = com.alibaba.druid.sql.SQLUtils.createSchemaStatVisitor(JdbcConstants.MYSQL);
            sqlStmt.accept(visitor);
            Set<TableStat.Name> names = visitor.getTables().keySet();

            for (TableStat.Name name : names) {

                String fullName = name.getName();
                if (fullName.endsWith("`")) {
                    int i = fullName.substring(0, fullName.length() - 2).lastIndexOf('`');
                    return fullName.substring(i+1,fullName.length()-1);
                } else if (fullName.lastIndexOf(".")!= -1){
                    return fullName.substring(fullName.lastIndexOf(".")+1,fullName.length());
                } else return fullName;
            }
        }
        return null;
    }

    public static String getDatabaseUrlByDatabaseName(final String host,final Integer port, final String databaseName){
        String url = "jdbc:mysql://"+host+":"+port;
        if(StringUtils.isNotBlank(databaseName)){
            url = url + "/" + databaseName;
        }
        return url;
    }

    public static void main(String [] args){
        String sql = "SELECT sys_menu.*\n" +
                "FROM sys_menu\n" +
                "\tINNER JOIN sys_role_menu ON sys_role_menu.menu_id = sys_menu.id\n" +
                "\tINNER JOIN sys_role_user ON sys_role_user.role_id = sys_role_menu.role_id\n" +
                "WHERE sys_role_user.user_id = ?\n" +
                "\tAND sys_menu.activate = true\n" +
                "ORDER BY sys_menu.menu_level ASC, sys_menu.menu_order ASC";
        String tableName = getTableName(sql);
        System.out.println(tableName);
    }
}
